PC Direct 1995 May

home *** CD-ROM | disk | FTP | other *** search

/ PC Direct 1995 May / PC Direct CD-ROM (May 1995).ISO / ipe / manuals / lc30 / lsc3.txt < prev next >

Wrap

Text File | 1994-06-29 | 83.7 KB | 1,910 lines

LAN Support Center Copyright 1994, McAfee, Inc. All rights reserved. ========================= Part Two: Crystal Reports ========================= 9.0 Introduction to Crystal Reports This chapter introduces Crystal Reports, shows you how to start the program, introduces you to some of the things the program can do, and familiarizes you with some of the "nuts and bolts" of using the program. 9.1 About This Part of the Manual This part of the manual contains all of the information you should need for building reports quickly and easily using Crystal Reports. It is divided into three chapters: Chapter Description 9.0 Introduction to Crystal Reports Introduction gives you a brief overview of Crystal Reports. It familiarizes you with the Crystal Reports screens, using the mouse, error messages, and the help facility. 10.0 Using Crystal Reports This chapter conceptually leads you through the process of creating a report with Crystal Reports. It suggests a methodology for creating reports starting with nothing more than a one sentence statement of purpose for the report. 11.0 Practical Crystal Reports Practical Crystal Reports is a "How to" chapter. It contains a variety of report creation topics and additional information on the practical aspects of using Crystal Reports to solve typical reporting problems. For information on topics not found in these chapters, refer to the Crystal Reports Help system. This part of the manual assumes that you understand the basic concepts and usage of Microsoft DOS or PC-DOS, and Windows 3.0 or higher. The procedures also assume that you will be using a mouse in the creation of your reports. While the instructions occasionally indicate keyboard methods for using the program, the instructions are generally mouse-oriented since most Windows users have a mouse. 9.1.1 Special Notations Used in this Part The following notations are used throughout this part of the manual: o Delete means the Del key on your numeric keypad. o Escape means the Escape or Esc key on your keyboard. o Enter means the Enter, Return, or CR key, depending on which of these keys appears on your keyboard. o Click means to click the left mouse button one time. o Double click means to click the left mouse button twice, in quick succession. o The term Cursor Keys means the Pg Up, Pg Dn, and Arrow Up, Arrow Down, Arrow Right, Arrow Left, Home, and End keys on your numeric keypad. o Ctrl+Key, Shift+Key, and Alt+Key are examples of the notation for two key combinations. They mean to press the first key in the combination (Control, Shift, or Alt), and, while keeping it depressed, to press the second key in the combination (designated as Key). o Text enclosed in double brackets (for example, << information >>) is intended to expand or explain the information that it follows. o Field names in formulas are italicized. o Insert|Formula means to select the Formula option from the Insert menu. o Drag, when used in talking about field boxes, means to position the cursor on a field box, depress the left mouse button, and keep the button depressed while moving the field to a new position. o Drag, when used in talking about text, means to depress the left mouse button and, while it is depressed, move the I-beam cursor across the text of interest to highlight and thus select it. o Computer type indicates data that you are to enter using the computer keyboard. It is also used to show example formulas. 9.2 Starting Crystal Reports You can start Crystal Reports in two ways: o from the DOS command line o from the Windows environment. 9.2.1 From DOS To start Crystal Reports (and Windows at the same time) from the DOS command line, type: cd and the name of the directory into which you installed Crystal Reports. For example, if you installed Crystal Reports in the default Crystal Reports directory, type: cd \CRW and then press Enter. At the prompt, type: win CRW and press Enter. Windows and Crystal Reports are both started. 9.2.2 From Windows Once in Windows, select the group window that holds the Crystal Reports icon. o If you're using a mouse, double click on the Crystal Reports icon to start the program. o If you're using the keyboard, use the arrow keys to select the icon and then press Enter. You can also start Crystal Reports from within Windows by double clicking on the file name CRW.EXE in the File Manager. 9.2.3 The Crystal Reports Registration Dialog Box The Crystal Reports Registration dialog box appears whenever you start Crystal Reports unless you have registered your copy of the program with the company, received a serial number, and entered it in the Enter serial number... edit box to disable this opening dialog box. There are two good reasons to register your copy of Crystal Reports: o Registration entitles you to technical support should you ever require assistance in using the product. o Registration assures you that you will be notified whenever the product is upgraded to offer new features, benefits, and efficiencies. The Crystal Reports Registration dialog box options are as follows: o Enter serial number... edit box: Use this edit box to enter the serial number that Crystal Services sends you when you register the program. Be careful to enter the serial number correctly. o Proceed to Crystal Reports button: Use this button to close out the dialog box and go directly into the Crystal Reports program. The button will be useful until you receive and enter your registration serial number. At that time the Crystal Reports Registration dialog box will no longer appear when you start the program. o Edit Registration Info button: Use this button to enter/edit product registration information. The Edit Registration Info button takes you to the Crystal Reports Registration dialog box. Use this dialog box to enter your name, company name, and phone and fax numbers. Crystal Reports stores this information and displays it and/or prints it out automatically when you print out the registration form, call up system information (via the Help|System Information command), or send in a technical support request (via the Help|Technical Support Request command). o Print Registration Form button: Use this button to print a copy of the completed registration form. 9.2.4 How to Register Your Copy of Crystal Reports Use the following procedure to register your copy of Crystal Reports. 1. Click the Edit Registration Info button. The Crystal Reports Registration dialog box appears. 2. Enter your name, address, phone, and fax in the edit boxes provided, and click OK when finished You are returned to the Crystal Reports Registration dialog box. 3. Click the Print Registration Form button. Crystal Reports prints out a copy of your completed registration form. 4. Fax the form to Crystal Services at the number that appears on the form, or mail it to Crystal Services at the address that appears on the form. 5. In a few days, the company will mail or fax you back your serial number. 6. Enter the serial number in the Enter serial number... edit box, and click the Proceed to Crystal Reports button when finished. This takes you to Crystal Reports and disables the dialog box so it no longer appears when you start the program. 9.3 Quick Start If you are an experienced Windows user who wants to get right into the program, follow these steps to set up a Crystal Reports report for the first time after you install the program. 1. Start Crystal Reports by double clicking on the Crystal Reports icon in the Program Manager. 2. Select New Report from the File menu. 3. When the Choose Database File dialog box appears, select the first database you want to activate for your report and press OK when finished. The Crystal Reports Report Editor appears with Page Header, Details, and Page Footer sections set up on your report template. The sections are all blank initially. Create your report by inserting and formatting items in each of these sections. 4. The Insert Database Field dialog box appears on screen with the Report Editor. The Insert Database Field dialog box displays a list of all of the fields in the active database. To speed the entry of multiple fields, the box remains on screen until you click on the Done button. You can move the dialog box to a new location if you wish. 5. Select the first field you want to appear on the report. A rectangular insertion cursor appears. 6. Position the cursor at the point in the Details section where you want your field to appear, and click the left mouse button to enter it. Crystal Reports marks the field position with a rectangular box. The characters in the box indicate whether the field is text (XXX...), number (555,...), dollar value ($555,...) date (YYYY-M...), or Boolean (T/F). The number of characters in the box indicate the number of characters allowed for the field in the database from which it came. 7. Repeat Steps 5 and 6 until you have placed all the fields you want to place. 8. To create a title, select Insert|Text Field, type in the information you want to appear, click Accept when finished, and position the field where you want it in the Page Header section. You can also insert database fields or special fields in that section from the Insert menu. 9. To see how your results will print, select Print To Window from the Print menu. Close the window when you are finished with your review. 10. If you want to: o change the placement or width of a field o format the field o insert a subtotal or grand total for a field o delete a field click the field box for that field. Black handles appear on the right and left sides of the field box to indicate that it has been selected. o To change the placement of the field, drag the field box to its new position using the mouse or the arrow keys. The arrow keys move the field box one grid position each time you press them. o To change the width of the field, drag the right or left handle using your mouse or use a Shift-Arrow combination on your keyboard. o To format or subtotal the field, click the right mouse button while the cursor is inside the field. A pop-up menu appears listing your various options. - To change the font, select Change Font and refine your selection using the Font dialog box when it appears. - To change the format (alignment within field; number, currency, or date display; etc.) select Change Format and refine your selection using the Field Format dialog box when it appears. - To insert a subtotal, select Insert Subtotal and refine your selection using the Insert Subtotal dialog box when it appears. In this dialog box you select the field and the condition that triggers a new subtotal whenever the field's value changes, and you select the sort direction: Ascending (A to Z, 1 to 9) or Descending (Z to A, 9 to 1). The program creates a new section to hold the subtotal. NOTE: The program automatically sorts the data (based on the field that triggers the subtotals) before it subtotals. Manually entering a subtotal sort is not necessary. - To insert a grand total, select Insert Grand Total. The program creates a new section to hold the grand total. - To delete the field, select Delete Field. 11. To create a formula to make data calculations or comparisons, select Formula Field from the Insert menu. Enter a name for your formula in the Insert Formula dialog box, and enter the formula itself in the Formula Editor when it appears. Enter fields, operators, and functions by selecting them from their respective scroll lists. You can get complete information on each available Function and Operator via the Help button, and you can check your formula syntax via the Check button. Entering a Crystal Reports formula is similar to entering a formula in a spreadsheet cell. When finished editing, select Accept and place the formula just like you do a database field. 12. To change the sort order, select Record Sort Order from the print menu. Select the field(s) you want Crystal Reports to use for sorting the report data. 13. To change the sort and group by field, select Group Section from the Edit menu. Select the group section of interest from the list that appears in the Edit Group Section (sections) dialog box, and select the new "trigger" field from the Edit Group Section (edit) dialog box when it appears. 14. If you want to limit your report to specific records (for example, the records of California customers that have YTD sales greater than $10,000), click the first field on which you want your selection to be based (in this case the State field) and choose Select Records from the Print menu or the right mouse button pop-up menu. Answer the questions that appear in the Select Records dialog box and click OK when finished. If your selection is based on more than one field, repeat the process with the remaining field(s) until you have completed entering your selection specifications. 15. When finished, you can print your report by selecting Print To Printer from the Print menu. 9.4 About Crystal Reports Crystal Reports is a powerful yet easy to use program for creating custom reports, lists, and form letters using data from your existing databases. The program works by establishing connections with one or more of your databases. Using these connections as conduits, Crystal Reports draws in the values from database fields you select and uses them in the report, either in their original form or as part of a formula that generates more sophisticated values. Crystal Reports was designed to work with all kinds of data: numbers, currency, text, dates, and Boolean (Yes/No) fields. It has a wide range of built-in tools that you can use to manipulate that data to fit your needs. Using these tools, you can: o make calculations and comparisons of data values, o calculate subtotals, and grand totals of field values, o calculate group averages, count the records in a group, and test for minimum and maximum values, o test for the presence of specific values, o present data only if certain conditions are met, o evaluate logical relationships between values, o convert data from one type to another, o merge text with other text, o merge text with data field data, and o perform numerous other useful, data-related activities. The data can be placed wherever you want it on the report, highlighted with special fonts and font sizes. With Crystal Reports, your reports can be as simple or as complex as your needs demand. 9.4.1 How Crystal Reports Prepares Reports Crystal Reports prepares reports by reading data in the database files you have selected and making that data available for use in your report. o If you wish to use data in a single data file, you simply choose that file when you create the report. o If you wish to use data in multiple data files, you need to select the files and also to indicate the field or fields in each file that Crystal Reports is to use to match data. NOTE: Crystal Reports does not write data to your original data files. Your original files remain unchanged using Crystal Reports, no matter how much you might manipulate the data you select. 9.4.2 Databases that Work with Crystal Reports Crystal Reports can build reports using the standard data files generated by dBASE for Windows and Paradox for Windows as well as with DOS versions of dBASE and Paradox. 9.5 The Crystal Reports Window The Crystal Reports window is simple and easy to understand: o the Title Bar appears at the top of the window, o the Menu Bar appears just below the Title Bar, and o the Button Bar appears just below the Menu Bar. 9.5.1 Title Bar The Title Bar displays the name of the program running in the window, Crystal Reports. You can use the title bar to activate the window (if the window is buried in a cascade of windows) or to move the window (by depressing the left mouse button with the pointer on the title bar and then, while the button is still depressed, dragging the window to a new location), or to maximize the window (by double clicking on the title bar). 9.5.2 The Menu Bar The Menu Bar is Crystal Reports' command center. Each option on the menu bar calls up a drop down menu of commands that you can use to create, modify, print, and save your reports. Each menu is listed in the table below: File The File menu includes commands you can use to open, close, and save files, to save files under a different file name, print the file to a printer, and create new report files. It also includes a command you can use to exit Crystal Reports. Additionally it contains a command that allows you to configure Crystal Reports to your specifications. Edit The Edit menu allows you to modify aspects of your report. The menu includes commands you can use to edit formulas, to edit and delete group sections, and to change summary operations. It also contains commands for cutting, copying, and pasting text, clearing (deleting) report elements, toggling the display of field names on and off, and changing your report title. Insert The Insert menu is the central menu you use for creating reports. The menu includes commands you can use to insert database fields, text fields, and formula fields; subtotals, grand totals, summaries (counts, averages, etc.), and group sections; print date, page number, record number and group number fields; and graphics, lines, and boxes. Format The Format menu includes commands for changing the look of the elements in your report. It includes commands for changing fonts, and formatting fields, report sections, graphics, lines, and boxes. The menu also provides commands for adding field borders as well as background color and drop shadows for your fields. Data- The Database menu is used to select and delete databases for use base with your reports, to change the alias you use to identify the database, and to link and unlink databases. It also has a command, File Location, for directing Crystal Reports to look for database files in new locations. Two verify commands can be used to adapt your reports to minor changes in database structure. Print The Print menu includes commands that let you print your report to a print window, to a disk file, or to a printer, print the report definition (a report describing your report), select the records or groups to be included in your report, select the order in which report data is to be sorted (by record or by group), and select a printer if you want the report to print on something other than the default printer. Window The Window menu includes commands that let you rearrange icons and windows. It also lists the report windows that are open and includes a command that lets you close all report windows at once, if desired. Help The Help menu includes a command that takes you to Crystal Reports' main help index, a command that will give you information about the Crystal Reports version you are using, and other help-related topics. 9.5.3 The Button Bar Crystal Reports groups several commonly-used commands on a Button Bar that remains on screen at all times (unless you choose to turn it off). Each command is represented by an individual button, and each button displays a graphic that visually describes the command. You activate Button Bar commands by clicking the appropriate button one time with the left mouse button. The Button Bar eliminates some of the steps needed to activate the included commands, and it can thus greatly speed your work in creating reports. 9.5.4 The Status Bar The status bar at the bottom of the report window displays valuable information to help you use Crystal Reports more efficiently: o Button bar functions: When the cursor is over a Button Bar button, the Status Bar displays a short description of the button's function. o Menu command descriptions: When you highlight a menu command, the Status Bar displays a short description of the command. To highlight a menu command, click the menu name and move to the command using the Down Arrow key. o Current selections: When you select or place a graphic, field, text field, graphic line or box, special field, or formula, the status bar displays the name of the item selected. It displays: - the file name for a graphic, - the alias and field name for a field, - the text in a text field, - the words Line for a line and Box for a box, - the field type for special fields (PrintDate, RecordNumber, etc.) - the formula name for a formula. o Graphic Coordinates: When you select or create a bit-mapped graphic, a graphic box, or a graphic line, the program displays the coordinates for the left, right, top, and bottom sides of the graphic element. 9.6 Right Mouse-Button Capabilities When you are working in the Report Editor, you can speed up your work considerably using Crystal Reports' right mouse button capabilities. When the cursor is positioned on a report element (a field, a group field, a formula, etc.) and you click the right mouse button, Crystal Reports displays a pop-up menu right next to the element. Unlike Crystal Reports' standard menus that group commands by function (editing, inserting, etc.), these pop-up menus are element-specific: that is, they contain only those commands from Crystal Reports' primary menus that are available for use with the selected element. The pop-up menus are valuable because: o they display the name and source (alias) of the element at the top of the menu so you can identify the elements on your report with a single mouse click, o they appear right next to the selected element making them quicker and easier to access than Crystal Reports' main menus, o they contain only the commands you need; you don't need to search for commands on a more comprehensive menu, o they make it easier to learn Crystal Reports because: - they eliminate the need to remember where to find a command, and - they make it easier to pick the right command since you're dealing with only a compact list of, and o they spotlight the things you can do with an element making it an easier system to use when you are under pressure or distracted. Use the following procedure to use the right mouse button menus: 1. Position the cursor on the element of interest. 2. Click the right mouse button. The pop-up menu appears. 3. Select the command of interest from the pop-up menu. These options work exactly like the corresponding options that appear on Crystal Reports' primary menus. For information on using any of these menu options, see the corresponding option in Crystal Reports Help. NOTE: If you have swapped left/right mouse buttons via the Control Panel, the left mouse button will activate the pop-up menus. 9.7 Crystal Reports Report Windows When a Crystal Reports report appears, it appears in a report window. A report window has a title bar, minimize and maximize buttons, and a control menu box like the Crystal Reports window. Unlike the Crystal Reports window, the report window does not have its own menu. Instead, the commands from the Crystal Reports menu remain visible and are active for the document window. Also unlike the Crystal Reports window, the document window has scroll bars at the bottom and along the right hand edge. Usinh these scroll bars you can reveal parts of the document that aren't immediately showing in the window. 9.8 Getting Help with Crystal Reports Crystal Reports offers a comprehensive help system to support you during every step of the report development process. 9.8.1 Context Sensitive Help Context sensitive help gives you immediate help with a screen element of interest (menu command, dialog box, etc.). The program takes you directly to the help information you seek without the need to traverse the indexing system. NOTE: To get help on a specific menu command, click the menu name and then use the Down Arrow key to highlight the command of interest. Once the command is highlighted, press F1. 9.8.2 Indexed Help Indexed help takes you to the help indexing system, a hierarchy of indexes which is designed to help you target your topic of interest. Using the indexing system, you can select a broad topic of interest from the first index and then narrow your selection using the next index that appears, then the next. etc. 10.0 Using Crystal Reports This chapter provides a structured approach to preparing reports with Crystal Reports. 10.1 Using Crystal Reports The approach of this chapter in preparing reports with Crystal Reports includes the following elements: o deciding on the content of your report, o developing a prototype on paper, o setting up the prototype using Crystal Reports, o manipulating the data with formulas and functions, o grouping, summarizing, and sorting your data, o editing and formatting the data, o specifying the records/groups to be included in the report, and o printing the finished report. This is not a hands-on chapter, but an important introduction that can make your report creation work more efficient and more satisfying. The chapter has been designed to provide you with a conceptual understanding of Crystal Reports as well as a brief introduction to Crystal Reports' powerful features. 10.2 Deciding on the Content of Your Report Before you do anything else, you should outline the information you want your report to provide. Use the following list of questions as a guide in making that outline: o What is the overall purpose of the report? o Who is going to read the report? o What is the report title going to be? o What information do you need besides the title to identify the report? - Where is that information to come from? - If the information exists in a database, what types of fields is the data stored in: number, text, etc.? o What identifying information do you want to appear at the bottom of each page? - Where is that information to come from? - If the information exists in a database, what types of fields is the data stored in: number, text, etc.? o What specific data do you want to appear in the body of the report? - Where is that data to come from? - Does that data exist in data fields or does it need to be calculated from data field values? - What kind of fields is the data stored in: number, text, etc.? - Do you want to show totals? - What kind? - What do you want to total? o What information, if any, do you want flagged on the report? - How do you want it flagged? o What information do you want highlighted in some way so that it really stands out? o Do you want the report to be based on all records in the database or only on specified records? 10.2.1 Purpose What is the overall purpose of the report? Reports are management tools. Their purpose is to help individuals quickly grasp the essential elements and relationships found in raw data so they can make effective decisions. For a report to be effective, it has to present the right data in a logical way. If it presents the wrong data or if it presents the right data in a haphazard manner, the report may slow the decision making process or even encourage incorrect decisions. A good starting place in the development of a report is to write out the purpose of the report in a sentence or two. The purpose statement helps you focus on your primary needs, and it gives your report both a starting point and a goal. Here are some examples of purpose statements: o The purpose of this report is to show monthly and year-to-date sales by sales rep, compare this year's numbers to last year, and flag reps who are seriously deficient. o The purpose of this report is to show sales activity for each item in inventory, and to suggest reorder quantities based on that activity. o The purpose of this report is to calculate bowling averages and handicaps for each member of the bowling league. Clarifying the purpose of the report before you start is a critical step in the overall process. A report without a clear purpose is like a meeting without a clear agenda; it rambles and accomplishes little. 10.2.2 Readers Who is going to read the report? A single report is often used by many individuals. A detailed, company-wide sales report, for example, may be used by sales reps, the regional sales manager, the national sales manager, and the Chief Operating Officer (COO). Each of these individuals will be interested in different aspects of the report. o A sales rep will use the report to evaluate his/her individual sales performance and to compare that performance to that of other reps in the region. o The regional sales manager will use the report to evaluate the reps in his/her region and to compare the region's performance to that of other regions. o The national sales manager will use the report to evaluate the performance of his/her regional managers and to compare overall sales to the current sales forecasts. o The COO will use the report to evaluate the performance of the Vice President of Marketing and the sales department as a whole, and to project such things as manufacturing needs, warehouse locations, etc. Since each of the users of the report has different interests, it is important to plan the report so it includes the information each of the users is looking for. 10.2.3 Title What is the report title going to be? Write out a working title for your report. You may decide to change it later, but at least you will have a title to use when creating your prototype report. 10.2.4 Other Header Information What information do you need besides the title to identify the report? Are you going to include identifying information in addition to your report title? You may wish to include the current date, information on who prepared the report, a block of text to describe the purpose of the report, the range of data covered, etc. If you are going to include such information, write it down so you can use it in preparing your prototype. 10.2.5 Header Information Sources Where is that information to come from? The information can come from a variety of sources, depending on the kind of information you plan to use. For example, the current date can be inserted using the Crystal Reports Insert|Print Date Field command. Information on who prepared the report might be drawn from individual data fields in the database(s) used. (If it is to be drawn from a database, what database? Or, what combination of databases?) A block of text can be created and entered on the face of the report itself. As you begin to think of where the information is to come from, you begin formally structuring the report. 10.2.6 Data Types in the Header If the information exists in a database, what types of fields contain the data: number, text, etc.? Crystal Reports uses different rules for working with different types of data. You will find it helpful later if you note the data type of each piece of data you plan to draw from a database. 10.2.7 Footer Information What identifying information do you want to appear at the bottom of each page (page number, report name, author's name, "Confidential," etc.)? 10.2.8 Footer Data Sources Where is that information to come from? 10.2.9 Data Types in the Footer If the information exists in a database, what types of fields is the data stored in: number, text, etc.? 10.2.10 Report Body Data What specific data do you want to appear in the body of the report? When you think of a report, it is probably the body of the report that you think of. The body should contain all the data that you need to fulfill the statement of purpose you wrote for the report. It should also contain all of the data needed by the various users that you have identified. You might find it helpful to list first the basic data that is required to fulfill the purpose statement, and then list the more specific kinds of data needed by the various users. 10.2.11 Body Data Sources Where is that data to come from? This step requires you to look at the available databases. Crystal Reports allows you to combine data from different databases to create your reports, so you have a great deal of flexibility in your work. o Much of the data in a typical report will be taken directly from data fields. Which data fields will you be using and where are they located? o Other data will be calculated based on data fields. Which data fields will be used in the calculations? o Still other data will be input directly into the report (headings, text blocks, etc.). Which data will you enter directly on the report, without drawing it from data fields? 10.2.12 Existing or Calculated? Does that data exist in data fields or does it need to be calculated from data field values? Some report information can be drawn directly from data fields (sales information, for example); other information will have to be calculated based on data field values (sales commission, for example, based on the relationship of sales to quota). In your planning, it can be helpful to segregate or flag data that needs to be calculated from that which can be used directly. 10.2.13 Data Types in the Body What kinds of fields contain the data: number, text, etc.? While it is important to understand data types for all data you will be using, it is of critical importance that you know the data type for data fields that will be used in calculations. Functions and operators work with specific kinds of data, so it's important to know the data type to know which functions and operators you can use in your calculations. 10.2.14 Groups Do you want your data organized into groups? How do you want it grouped? By customer? By date? 10.2.15 Group Values Do you want to show a subtotal at the end of each group? A count? An average? Crystal Reports allows you to specify several kinds of group values. 10.2.16 Group Value Positions Where do you want the group values to appear. With the group data? With the group data but on a page separate from other groups? Only at the bottom of the page? Crystal Reports gives you all of these options. 10.2.17 Grand Totals, Grand Total Averages, etc. Do you want to total (or average, count, or determine the maximum or minimum value included in) all the values in any column on your report? Crystal Reports allows you to do this and place the grand total (or the grand total average, grand total count, etc.) at the bottom of the selected column. 10.2.18 Flags What information, if any, do you want flagged on the report? You may want to call attention to some data by flagging it on your report. For example, non-moving inventory items are often flagged on inventory reports so they can be given special attention. If you want any information flagged, identify the information and the parameters for flagging. Using the inventory report example, you might want to flag each item that has shown no activity during the last month, during the last three months, or during some defined period. 10.2.19 Flag Options How do you want it flagged? You may want to flag items with an asterisk or some other symbol, or you may want a word to appear as a flag. In any case, you should write out flagging instructions so they are handy. 10.2.20 Highlights What information do you want highlighted in some way so that it really stands out? Crystal Reports gives you the opportunity to underline report elements, or to change the fonts or font size used for specific report items. All of these formatting tools can be used to highlight key data on a report. If you have data that you want highlighted, you should write down highlighting instructions so they are handy too. 10.2.21 Record or Group Selection Do you want the report to be based on all records or groups in the database or only on specified records or groups? Crystal Reports gives you the opportunity to base a report on all records in a given database, or on a limited set of records from the database. Using Crystal Reports you can select records based on simple date ranges or comparisons, or you can create complex formulas to identify the records to be included. Take a few minutes to determine the records needed for your report and list the criteria to be used for selecting those records. 10.2.22 Sorting Do you want your data sorted based on record or group values? Crystal Reports gives you both alternatives. 10.3 Developing a Prototype on Paper Graphic designers generally begin their work on a magazine cover, brochure, or display advertisement with a rough pencil sketch. They often use boxes, circles, or other symbols to represent the graphic elements they intend to include in the final product, and they often use lines or scribbles to represent text. Doing the rough design on paper, in pencil, helps them create a "look" for each page. It helps them find a balanced way of positioning the various elements before they begin working with sophisticated graphics tools. We think you will find a similar exercise helpful in designing your Crystal Reports reports. While a paper prototype is useful regardless of your expertise with Crystal Reports, it is particularly valuable when you are first learning to use the Crystal Reports program. With the paper prototype at hand, you can put your full effort into learning and using the Crystal Reports commands instead of trying to design and learn at the same time. Use the following procedure to design a paper prototype with Crystal Reports: o Get paper of the size you'll be using for your finished report. o Position your title and other descriptive header information, using boxes or lines to represent report elements. o Position your footer information. o Review the page for balance. o Look at the information you intend to include in the body of your report. - Count the number of fields you will be using and estimate the appropriate spacing between fields. - Use rectangles to pencil in the fields using your estimated spacing. - Change the spacing if necessary. - Decide on a logical sequence for presenting the report data. - Label the fields to indicate that sequence. o Use small boxes to indicate group values and totals. o Place some random flags where you want the flags to appear. o Darken any elements you want highlighted so they stand out from the rest of your prototype. o Review your finished product for look and balance, and make changes as needed. 10.4 Setting Up the Prototype Using Crystal Reports Once you have completed your paper prototype, it is a straightforward process to recreate that prototype in the computer, using Crystal Reports. Before you do, it is important to understand how Crystal Reports' report editing process works. NOTE: This section assumes that you are new to Crystal Reports and that you plan to build a simple report using data from a single database. No advanced topics are covered in this section. When you want to begin a new report, Crystal Reports displays the Choose Database File dialog box. You use this dialog box to select the database file(s) that contain the data you want to use in your report. You will first select the directory and, once in the directory, you can select the database file you identified earlier in Deciding on the Content of Your Report. 10.5 The Report Editor Once you select your database, Crystal Reports displays the Report Editor screen. You use this screen to insert and format data and to watch your report take shape. When you open a new report, Crystal Reports automatically creates three sections in the Report Editor: o A Page header section - this section is generally used for the report title, field headings, and other information that you want to appear at the top of each page. o A Details section - this section is the body of the report. The bulk of your report data will generally appear in this section. o A Page footer section - this section usually contains the page number and any other information that you want to appear on the bottom of each page. The name of each section appears in the gray area to the left of the Report Editor edit box. o You build your report by inserting data fields, formulas, and other report elements (record counts, record numbers, etc.) in the Details section of the editor. You use the Insert menu, in most cases, to select or create the elements you want to insert on the report. The Report Editor uses rectangular element markers to indicate the size, position, and data type of the report elements you have inserted. o You add subtotals (and other group values) by selecting a field to subtotal and then telling Crystal Reports the conditions that are to generate a new subtotal or group value (change of customer number, change of sales rep, etc.). Crystal Reports creates group value sections as needed and places the group value in the section. Again, Crystal Reports uses rectangular field boxes to represent the group values. o You insert grand totals in the Grand Total section. This section appears when you select the field to total and then select Insert|Grand Total. Crystal Reports uses a rectangular field box, this time to identify the field in the Grand Total section of the Report Editor. o You can add freeform text anywhere on the report by positioning the text cursor in the section in which you want the text to appear, typing in the text, and then using the Tab key to move it into position. You can also type freeform text in a text field; this allows you to avail yourself of additional formatting options (alignment, hide options, etc.). 10.5.1 Auto-Scrolling Capabilities The Report Editor has auto-scrolling capabilities. That is, whenever the cursor hits one of the edges of the Report Editor when you are placing, resizing, or moving a field or drawing a graphic line or box, the Editor automatically scrolls to reveal more workspace (if more is available). 10.5.2 Resizing Sections The Report Editor first appears with default section sizes. You can expand or reduce report sections by dragging the lines that separate the sections. When you position the I-beam cursor over one of those lines, the cursor changes to a double-arrow resizing cursor. Once that cursor appears, you can resize as needed. Alternately, to expand a report section, you can click the section of interest and press Enter as many times as needed. Once you've expanded a section, you can reduce it by deleting unneeded lines with the Backspace key. 10.5.3 A Word about Databases, Records and Fields Before we go any further, a discussion of databases, records, and fields is in order. A record is the basic building block of a database. Each record contains data about a single entity (a customer, an order, etc.), and each database contains at least one record. The data in each record is stored in fields (holding spaces). Each field holds one piece of data known as a value. The database from which you will create a report is a collection of related records. A customer database, for example, is a collection of records that each contain data on a single customer. 10.5.4 Records and Fields in Row/Column Reports Think of the data in a database as being stored in horizontal rows and vertical columns. CustNumber Fname Lname 01034456 Bill Brown 01034457 Jane Doe 01034458 Bob Jackson 01034459 Mary Jones The table above shows field values for four records from such a database (in this case a customer database). o Each row represents one record. All of the data in that row is about a single customer, but the data is broken into three distinct pieces: customer number, first name, and last name. Each of those pieces represents a value found in a field on that record, the CustNumber, Fname, and Lname fields respectively (as indicated by the column headings). o Each column represents one field. All the data in a given column represents the values that appear in that field on each of the records in the database. The structure of a row/column report corresponds to the structure of a database with rows representing individual records and columns representing individual fields. 10.5.5 Records and Fields in Data Block Reports Some reports don't follow the row/column format, however. Instead, some group related data in data blocks. A mailing list, for example, typically uses several rows for each record: a row for the company name, a second row for the street address, a third row for the city, state and ZIP code, etc. In such a report all the data for a given customer in the list appears in a block. Each block represents a single record even though the data in the block spans several rows. 10.5.6 Data Types The data type of a field, (string, number, currency, date, Boolean, or memo) determines the type of information that can be stored in that field and which will print in the report column. Fields of each data type display on your screen like this: XXXXXXX String - for example, a company name, account description or customer name. $5,555,555.55 Currency (Paradox/Btrieve files only) - for example $500.00 or $50,000,000.00-. 5,555,555.55 Number - for example 120 or 5555. YYYY-MM-DD Date - for example Oct 10 90. YES/NO Boolean (YES/NO) data fields - for example, the result of a formula which compares a customer's credit limit to see if it is greater than $5000 and prints YES if the credit limit is more than that amount; NO if it is less than that amount. XXXXXXX Memo - a paragraph describing a piece of property, comments regarding a job applicant, a summary of a movie plot, etc. 10.5.7 Formatting, and Record and Group Selection While formatting, and record and group selection will be covered in depth later, there are a few things about those subjects that are worthwhile to cover here: o Currency, date, and Boolean fields are shown above in the default format. This format is used for both the element markers (in the report editor) and for the report elements themselves (in the report). If you want your data to appear in a different format on your report, you can make the desired changes using the Format|Field command. NOTE: The characters in the element markers change to reflect font changes and many formatting changes (alignment, etc.). o The width and position of a field (when you place it in the Report Editor) determines the initial spacing between fields and the order in which the fields will print across the report page. o When you insert a data field in your report, Crystal Reports assumes that you want to pull data from all the data records in the selected database. To limit the records (or groups of records) used in your report, you need to tell Crystal Reports how to identify the records or groups of records you want to include. Using Edit Record Selection Formula or Edit Group Selection Formula on the Print menu, you can build the selection formula that fits your needs. 10.6 Building Your Prototype To build your report you will follow these steps: o Select the database(s) you wish to use in your prototype report. o Enter and position the data fields, text, and titles you want included in the header, footer, and body of your report. o Print your report to the Print Window and review your work. o Make whatever changes are called for. o Insert your totals, subtotals and other group fields. o Enter and position any formula fields that will calculate or manipulate data or create flags. o Print your report to the Print Window and review your work. o Format the report elements that you want to stand out in some way from the others. 10.6.1 Selecting the Database(s) You use the File|New Report command to begin a report from scratch. That command leads you to a dialog box from which you can select the first database you wish to use. To select additional databases, you use the Database|Add File to Report command. 10.6.2 Entering and Positioning Data Fields Enter and position data fields using the Insert Database Field dialog box that lists available fields in the selected database. Make your selections from the list, and then place the field in your report using the rectangular field placement cursor that appears. 10.6.3 Adding Text You can add text to your report by typing it at the text cursor and then moving it into place. Move the I-beam cursor to the line on which you wish to enter text and click the left mouse button to set the text cursor at the left edge of the Report Editor. Then, simply type in your text, move the I-beam cursor in front of it, and push it into position using the Tab key or Space Bar. You can also add text by inserting text fields and positioning them as you would any other field. 10.6.4 Entering Field Titles By default, Crystal Reports automatically inserts a field title whenever you insert a new field or formula field. The title is a text field that can be edited. The left edge of the title field comes aligned with the left edge of the field it identifies. To toggle this feature off you use the File|Options command. 10.6.5 Printing and Reviewing You can print and review your work in progress at any time using the Print|Print To Window command. When you select this command, Crystal Reports prints your report to the print preview window. 10.6.6 Making Changes Move a database field drag it to a new position with the mouse, or by selecting it and using the Arrow keys. You can move a field within its originating section or to other sections of your report if you wish. To delete a field select it and then press the Delete key or select Edit|Clear. NOTE: To select multiple fields at one time, press the Shift key and, while keeping it depressed, click the various fields you want to select. Handles will appear on each field selected. When finished, you can move or delete the selected fields as a group. 10.6.7 Placing Totals, Subtotals, and Other Group Values To place grand totals select the numeric or dollar field you want to total, and then select Insert|Grand Total from the Menu Bar. Select group values (subtotals, group averages, group counts, etc.) by first selecting the field you want grouped. Then select the kind of group value you want, using the Insert|Subtotal or Insert|Summary commands. Select the kind of group value you want (if you selected Insert|Summary). Then select the field that the program uses to sort and group your data by and the sort direction. 10.6.8 Moving Group Values To move a subtotal or summary drag it to a new position with the mouse, or select it and move it with the Arrow keys. You can move subtotals and summaries only within their originating sections, or from the group footer section to the group header section for the subtotal or summary of interest. 10.6.9 Entering Formulas Enter formulas using the Insert|Formula command. This command calls up the Formula Editor. Using the Formula Editor, build your formula using fields, functions, and operators, and check your work using the built in formula checker. 10.6.10 Formatting You can change the font or point size using the Format|Font command. You can change the alignment and printing characteristics of field data using the Format|Field command, and you can change the conditions that trigger subtotals, add page breaks, suppress blank lines, hide selected sections, etc. using the Format|Section command. Once you have built a prototype report, you have a working model that you can customize to meet your specific needs. 10.7 Manipulating the Data with Formulas and Functions Crystal Reports uses formulas and functions to help you create reports more quickly and easily. It uses them also to allow you to do the kind of "number crunching" and data manipulations that are necessary for advanced reporting. 10.7.1 Formulas A formula is a set of instructions that may be used to calculate information you can't obtain directly from database data fields. For example: o A database record may have a Qty1 field and a Qty2 field but no field that sums both quantities. If you want the sum of these two fields to appear on your report, you need a formula that adds the value in one field to the value in the other. o A database record for a sales rep may contain the field GrossSales and you want to use 120% of that gross as the sales quota for the following year. To accomplish this you need a formula that multiplies GrossSales by 120%. o A database record for a one product company may contain a field Revenue that expresses total dollar sales for the year. You're interested in determining the number of units sold based on an average price of 49.95 per unit. To accomplish this you need a formula that divides Revenue by 49.95. All of these examples require simple formulas: {file.Qty1} + {file.Qty2}, 1.20 * {file.GrossSales}, and {file.Revenue}/49.95 respectively. These formulas all use Crystal Reports operators (+, * [multiply], / [divide]) and they're all easy to create and understand. But not all of your information needs can be reduced to simple formulas; some needs require extensive calculations or manipulations. For example: o You want to determine your average monthly unit sales for the last year, rounded to the nearest unit. o You want to convert ounces of inventory to pounds and ounces, and have it appear in the format x pounds y ounces. The formulas required to accomplish these activities require a fair amount of data manipulation: rounding, averaging, converting numbers to text, calculating remainders, etc. While some of the manipulations (averaging, calculating remainders) can be done using Crystal Reports operators alone, others can't be done without the use of functions. And even the ones that can be done without functions can be done more quickly and efficiently with them. (For additional information on formulas, see Crystal Reports Help.) 10.7.2 Functions A function is a preset procedure or subroutine used to evaluate, make calculations on, or transform data. For example: o the NumericText function evaluates the contents of a text field to see if it is a number, and o the UpperCase function transforms all lowercase characters in a string to uppercase. When you specify a function, Crystal Reports performs the set of operations built into the function without requiring you to specify each operation separately. For example: o the Average function sums a list of values and divides the sum by the number of values in the list. o the DayOfWeek function extracts the day component of a date, determines the day of the week the date falls on, and converts the day of the week to a number (1-7) where Sunday is the first day of the week. By performing multiple operations with a single command, these kinds of functions are a kind of shorthand that make it easier and less time consuming for you to create reports. But not all functions involve lengthy calculations; some simply allow you to do things that you couldn't do easily without them while others take the drudgery out of report creation. For example: o the ToNumber function converts a number that has been stored as text, to a number that can be used in calculations, and o TrimRight removes all the spaces to the right of a string of data stored left-justified in a database. 10.7.3 How Functions are Used Functions are used in formulas. In fact, a single function and its required argument(s) may include the entire formula. For example, Abs ({file.TestResult}) is a perfectly acceptable, stand-alone formula for calculating the absolute value of the field TestResult. 10.7.4 Function Syntax Each function comes with its own set of usage rules (syntax). These rules must be followed for the function to perform correctly. If they are not, Crystal Reports displays a Formula Compiler Error message. As an example, the correct syntax for using the Average([array]) function is: Average ([array]) << where array is an array of constants, field values or calculation results, separated by commas.>> Translated, this means that to use the Average([array]) function correctly, you should enter: Average followed by a list of numeric values, separated by commas, with the list of values enclosed in brackets, and with the array enclosed in parentheses. Thus: Average ([1,2,3,4,5]) is an example of the correct way to use the Average([array]) function. If you try to use the function: o without separating the values by commas, o without enclosing the values in brackets, o without enclosing the array in parentheses, o while including any unnecessary characters, or o with values that are not numeric, Crystal Reports won't accept the formula and will display a Compiler Error message identifying your error. 10.7.5 Entering Formulas and Functions Formulas and functions are entered via Crystal Reports' Formula Editor. The Formula Editor is a dialog box that contains all the tools you need to create and check the correctness of formulas. Using the Editor, you: o assign a name to your formula, o enter the formula, o check it to make sure you have entered it correctly, and then o accept it for use in your report. When you accept it, Crystal Reports places the formula in your report in the position you selected for it. Then, when you print the report, Crystal Reports prints the results of the formula instead of the formula itself. 10.7.6 The Fields Box You can enter fields into your formulas in two ways: via the Fields box, or manually. o To enter a field via the Fields scroll box, you move the I-beam cursor to the place you want to insert the field and click the left mouse button to set the text cursor at that point. Then you locate the field you wish to insert from the Fields scroll box list. - Fields already in use in the report are grouped at the top of the list; other available fields follow. - Formulas you have entered are listed next, their names preceded by the @ sign. - All other fields available in the active databases are listed last, grouped by database. Select a field. Crystal Reports inserts the selected field (complete with the required syntax elements) at the text cursor. NOTE: You select an item from the Formula Editor scroll boxes (Fields, Functions, or Operators) by double clicking it, or by clicking it once to highlight it and then clicking the Select button at the bottom of the Editor to complete the selection process. o To enter a field manually, you locate the text cursor in the appropriate position and type in the field name as you would any text. o The correct syntax for a database field name is: {file.fieldname} If you: - don't include the file name, - leave out the separating period, or - fail to surround the expression in braces, Crystal Reports will generate a Formula Compiler Error message detailing your error. o The correct syntax for a formula field is: {@formulaname} 10.7.7 Operators and Functions Boxes You can also enter operators and functions into your formula in one of two ways: manually or via the Operators and Functions scroll boxes. o To enter an operator or function manually, locate the text cursor in the appropriate position and type in the operator or function as you would any text. o To enter an operator or function via the lists in the Functions and Operators scroll boxes, set the text cursor where you want the entry to appear in the formula. - Then select the item of interest from the scroll box lists. Crystal Reports inserts the selected item in your formula, complete with any parentheses, brackets, or commas required. NOTE: For an array of items, only the first comma is included. As you enter additional items into an array, you will need to type in commas to separate the items. 10.7.8 Order of Precedence When entering formulas that contain different kinds of operators, it is important to consider order of precedence, the order in which Crystal Reports performs the operations in your formula. You learned simple order of precedence in high school math: when performing calculations, do multiplication and division first, then addition and subtraction. Thus: 5 + 10 X 3 = 35 The calculation 10 x 3 is performed first to get 30. 30 is then added to 5 to arrive at the final answer. Now if your intention is to add 5 to 10 and then multiply the sum by 3, you have to modify the order of precedence with parentheses. You can do that thus: (5 + 10) x 3 = 45 It's clear that parentheses have a higher precedence than the add, subtract, multiply, and divide operators. They redirect the order of calculation. You learned all of this in school and Crystal Reports follows the same rules of precedence. But Crystal Reports uses many additional operators, and it's important for you to understand the precedence Crystal Reports assigns to each so you can write your formulas to perform as expected. o In the following list, Crystal Reports performs the top level operations first, then the second level, then the third, and so forth. o When it encounters two or more operations that are on the same level, it performs them left to right. Level 1 Parentheses, Array, IfThenElse Level 2 Call, Subscript Level 3 Identity, Negate, Dollar, Not Level 4 Multiply, Divide, Percent Level 5 Add, Subtract Level 6 To Level 7 Less than, Greater than, Greater than or equal, Less than or equal, In Level 8 Equal, Not equal Level 9 And Level 10 Or 10.7.9 Using Dates in Formulas Crystal Reports includes many useful functions for including dates and date ranges in formulas; you will learn more about those functions in Crystal Reports Help. At this point, however, it's useful to know how to enter any date into a formula. You enter a date simply by entering the Date function (manually or via the Function button) and then providing the date of interest in the parentheses that follow in the form YYYY,MM,DD (1992,01,01 = January 1, 1992). 10.7.10 The Formula Checker The Formula Editor also contains a formula checker which checks the syntax of your formula. If the syntax is incorrect, the formula checker points out the location of the problem and tells you what the problem is. You activate the formula checker by selecting the Check button. Crystal Reports also checks the formula automatically when you choose Accept. When debugging formulas, especially if you have entered them manually, look carefully for: o missing quotation marks around text strings, o missing database name accompanying field names, o missing braces around database name/field name combinations, o missing closing parentheses to match opening parentheses, o case differences (the Formula Checker is case sensitive), o if-then-else formulas that use different data types for the then and else actions, and o using X instead of * for multiplication. The Formula Editor is easy to use and can be called up by selecting Formula from the Insert menu. 10.7.11 Developing Complex Calculations with Formulas Crystal Reports allows you to combine fields, functions, and operators to create complex calculations and manipulations of data. For example, to test the drawing power of two different offers, you can use a single Crystal Reports formula to send one offer to all customers with an even customer number and a second offer to all customers with an odd customer number. You can do some remarkable manipulations using Crystal Reports formulas. 10.8 Grouping, Summarizing, and Sorting Your Data Crystal Reports allows you to group, summarize, and sort your data to achieve the reporting results you want. 10.8.1 Grouping the Data A group is a set of records that are related to each other in some way. In a customer list, for example, a group could consist of all those customers living in the same ZIP code, or in the same state. In a sales report, a group could consist of all the orders placed by the same customer, or all of the orders generated by a specific sales rep. Breaking data into groups (and then doing something to evaluate the group data, if you wish) is a key part of effective reporting. In fact, it's what separates a report from being merely a presentation of raw data and a valuable communication tool. Crystal Reports allows you great flexibility in grouping data. It also gives you the ability to create a number of different kinds of group values. 10.8.2 Group Values A group value is the value generated as the result of an evaluation, a tally, or a calculation performed on data from a single group. A subtotal is one kind of group value; it is the sum of all of the values from a single field, from all the records in a group. In a sales report, for example, if you subtotal sales by sales rep, Crystal Reports gathers all the records that belong to the sales rep and totals the sales amounts from all the records. In a group average, Crystal Reports averages the values in a group of records; in a group count, it counts the values in a group of records, etc. Group values are important tools for creating powerful reports. Crystal Reports gives you several alternatives for working with group data. It enables you to: o calculate the sum of values, o calculate the standard deviation or variance of the values, o average the values, o count the values, o determine the highest value, and o determine the lowest value in the group. 10.8.3 Grouping Data with Crystal Reports While there may be many data fields on a report, there is typically only one field for which you are interested in grouping the data. In a sales report, for example, it would probably be the field listing the amount of sales; in a commission report, it would probably be the field listing the amount of commission, etc. To group data you first select the field you want to group together. Once the field is selected, you select the action you want to take on each group of data from that field: o If you want to simply group the data and take no further action, you can select Insert|Group. o If you want to subtotal it, you can select Insert|Subtotal. o If you want to average, count, determine the maximum or minimum value, etc., you can select Insert|Summary. Subtotals and summaries make up the category of group fields. NOTE: The Insert|Subtotal command is simply a shortcut for setting up a summary that adds the values in each group. Once you have selected the group field and the action, you select another field (a sort and group by field) that triggers a grouping whenever its value changes. In grouping your data by state, for example, you would use the state field to create a new group (and generate a group value if selected) whenever the state changes. Likewise, the ZIP code field would trigger a grouping whenever the ZIP code changes. Once you've made these simple selections, Crystal Reports does the rest. Your data will be grouped, and the group value (if you have selected one) will be calculated and will accompany each group. 10.9 Summarizing the Data Crystal Reports provides three easy-to-use summarizing options: Grand Total A grand total adds all values in a field for the entire report and prints the sum on the last page. Subtotal A subtotal is a partial total, a total of a specific, limited group of data in a field. Summary A summary summarizes field data from a specific, limited group of records. It can total the data like a subtotal, but it can also average the values, count the values, or determine the highest or lowest value in each group or field. All of these options are available on the Insert menu. 10.9.1 The Summarizing Process The process of summarizing the data on your report follows these steps: 1. Selecting the field you want to summarize. 2. Selecting the summarizing option you want from the Insert menu. Finally, if you select Subtotal or Summary as a total type, the process will also include the following step: 3. Selecting the data field that triggers a new subtotal/summary whenever its value changes. 10.9.2 Selecting a Field to Total To select the field you want to summarize, click on it with a mouse. Crystal Reports puts handles on the selected marker to highlight it as selected. 10.9.3 Selecting a Total Type To select a total type, select it from the Insert menu or pop-up menu. 10.9.4 Selecting a Field to Trigger a New Subtotal If you select Subtotal or Summary as a total type, Crystal Reports asks you to indicate the field that is to trigger a new subtotal/summary whenever its value changes. NOTE: For additional information on subtotaling data (with examples), please see section 11.2.32 entitled "Sorting Your Data." 10.10 Sorting There is a logic to the way values are arranged when they appear in a column on your report. Initially, they are arranged in the same order as the data appears in your database. But data can be sorted in a variety of ways: o A mailing list, for example, could be sorted in ascending order, on the ZIP code field; that is, the customers would be sorted so that those with the lowest ZIP codes would appear first and those with the highest ZIP codes would appear last. o It could also be sorted in ascending alphabetical order, on the last name field; that is, customers with last names beginning with A would appear first and those with last names beginning with Z would appear last. o It could also be sorted by street address or customer first name if you had some practical reason for doing so. Crystal Reports gives you the opportunity to change the existing sort order using the Print|Record Sort Order and Print|Group Sort Order commands. 10.10.1 Sorting by Record When you use the Print|Record Sort Order command, Crystal Reports asks you to select two things: o the field you want your sort to be based on (sort field), and o the sort direction. 10.10.2 Sort Field A sort field is a field that determines the order in which data appears on your report. Crystal Reports sorts field data using Windows' sort comparison algorithms, and it uses rules specific to the Country you select in the International section of the Windows Control Panel. You can use any field as a sort field. A field's data type determines the method in which the data from that field is sorted: Text Text fields are sorted in the following manner: One character values are sorted so that blanks have the lowest value, then punctuation, then numbers, then uppercase letters, and finally lowercase letters. Then two character values are sorted, then three, etc. using the same rules. As a result: "BOB" comes before "bob", "123" comes before "124", " " (blank) comes before "a", and "aa" comes before "aaa" Dollars Dollar fields are sorted in numeric order. Number Number values (120, or 5555) are sorted in numeric order. Dates Date fields are sorted in chronological order. Booleans Comparison fields are sorted so that false values come first, then true. When you select a sort field, Crystal Reports sorts the values from that field. 10.10.3 Sort Direction Direction refers to the order in which the values are printed, once sorted. o Ascending order means smallest to largest, 1 to 9, A to Z, False to True. o Descending order means largest to smallest, 9 to 1, Z to A, True to False. 10.10.4 Single Field Sorts Single field sorts are sorts in which all the data in the report is sorted based on the values in a single field. Sorting an inventory report by stock number and sorting a customer list by customer number are examples of single field sorts. 10.10.5 Multiple Field Sorts In multiple field sorts, Crystal Reports first sorts the entries (alphabetic or numeric) in the first field selected, putting them in ascending or descending order as specified. Then it sorts any entries in the second field that can be sorted without disturbing the sort order of entries in the first field. It then sorts any entries in the third field that can be sorted without disturbing the sort order of the entries in the first two fields. It follows the same pattern for sorting additional fields. 10.10.6 Sorting Groups Crystal Reports allows you to change the order in which groups appear on your report. You can: o base the sort on any group (subtotal or summary) in your report, and o sort your report so that group field values appear in ascending or descending order. You change the sort order using the Print|Group Sort Order command. When you sort by group, nothing happens to the sort order of the records within a group; only the relative positions of the groups themselves change. NOTE: For additional information on sorting and examples of single field, multiple field, and group sorts, please see section 11.2.32 entitled "Sorting YourData." 10.11 Editing and Formatting the Data Crystal Reports makes it easy to edit and format your data. The editing/formatting process follows these steps: 1. Selecting the data you want to edit or format. 2. Selecting the editing or formatting action you want to take place. 3. Entering the specifics of the action in the dialog box/editor if/when it appears. 10.11.1 Selecting the Data To select the data element you want to edit or format, you click on it with your mouse. Crystal Reports draws handles on the element marker to highlight it as selected. 10.11.2 Selecting the Action Once the element is selected, you select the editing or formatting action from Crystal Reports' Edit or Format menus. For example: o to change the font used to print the data, select Font from the Format menu, o to hide the field when printing, select Field from the Format menu, or o to modify a formula, select Formula from the Edit menu. 10.11.3 Entering the Specifics In those cases where a dialog box or editor appears after you've selected an action, you enter the specifics of that action in the window that appears. For example: o in the Font dialog box, you enter the font and point size you wish to use, and indicate whether you want the data to appear in boldface or italics, or whether you want it to be underlined or overprinted with the strikeout character, and o in the Formula Editor, you make the changes you want to the formula that appears, check those changes if you wish, and accept the revised formula when you're finished. Crystal Reports takes it from there and performs the action you selected. NOTE: In some cases you have to specify a second action to complete the editing change desired. For example, to move some text in your report, you first cut the text from its current position, and then you paste it in its new position. In such a case you select the data and then the action (Cut) to cut the text to the clipboard. Then you mark the new position using the insertion pointer and select Paste to insert the text in the new position. 10.12 Specifying Records/Groups to be Included When you select a field to appear on your report, Crystal Reports, by default, prints field values from every record in the selected database. But in many cases you may not want to include all the values, but only a specific range of values. For example, you may want to include only a specific group of customers or a specific range of account numbers out of the total number of values in the database. Or you may want to include values from only those records that fall within a particular date range. With Crystal Reports this is easy. The program includes four commands on the Print menu for restricting your report to specific records or groups of records as described in the following sections: 10.12.1 Select Records This option allows you to limit your report to specific records that fit a condition or conditions you specify. It automatically builds a record selection formula using your responses to dialog box questions. This option requires no knowledge of the Crystal Reports formula language. 10.12.2 Edit Record Selection Formula This option also allows you to limit your report to specific records that fit a condition or conditions you specify. It takes you to the Formula Editor where you can manually build a record selection formula to fit your needs. This command is intended for users who are familiar with the Crystal Reports formula language. 10.12.3 Select Groups This option allows you to limit your report to specific groups that fit a condition or conditions you specify. It automatically builds a group selection formula using your responses to dialog box questions. This option requires no knowledge of the Crystal Reports formula language. 10.12.4 Edit Group Selection Formula This option also allows you to limit your report to specific groups of records that fit a condition or conditions you specify. It takes you to the Formula Editor where you can manually build a group selection formula to fit your needs. This command is intended for users who are familiar with the Crystal Reports formula language. You select the command, specify the records or groups of records you want included, and your report prints using only the selected records or groups. 10.13 Printing the Finished Report When you want to print your report or when you want to see what your report will look like when printed, use Crystal Reports' three printing options: o printing to the printer for hard copy output o printing to a print window to review your work o printing to a disk file for use with other applications All of these options are available on the Print menu. When you are creating a report, you will find yourself printing to the print window often, in order to check placement and formatting of the various report elements. The print window even includes a print preview function that lets you see each page of your report as a whole, top to bottom. Then, when you want to print a final or interim copy of the report for hands-on review, you can print to the printer for hard copy output. If you want to use your report data in another application (in a spreadsheet or word processor for example), you can print the report to a disk file in any of a variety of popular file formats. Once in a disk file, you can import the data into your other application following the importing procedures established by the receiving application.